﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class UserFileCabinetUploadFiletbDAO
    {

        /// <summary>
        /// 保存上传文件信息
        /// </summary>
        /// <param name="FileName"></param>
        /// <param name="FilePath"></param>
        /// <param name="fileUploadTime"></param>
        /// <param name="fileCabinetId"></param>
        /// <returns></returns>
        public int saveUploadFile(string fileName, string filePath, DateTime fileUploadTime, int fileCabinetId)
        {
            int fid = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into UserFileCabinetUploadFile values(@FileName, @FilePath, @fileUploadTime, @fileCabinetId)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@FileName", fileName));
                sqlCmd.Parameters.Add(new SqlParameter("@FilePath", filePath));
                sqlCmd.Parameters.Add(new SqlParameter("@fileUploadTime", fileUploadTime));
                sqlCmd.Parameters.Add(new SqlParameter("@fileCabinetId", fileCabinetId));
                sqlCmd.ExecuteNonQuery();
                sql = "select @@identity";
                sqlCmd = new SqlCommand(sql, sqlConn);
                fid = (int)sqlCmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return fid;
        }

        /// <summary>
        /// 根据所属文件柜记录编号查找该记录的上传附件
        /// </summary>
        /// <param name="fcid"></param>
        /// <returns></returns>
        public List<UserFileCabinetUploadFile> findByFileCabinetId(int fcid)
        {
            List<UserFileCabinetUploadFile> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserFileCabinetUploadFile where FileCabinetId = @fcid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@fcid", fcid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<UserFileCabinetUploadFile>();
                    while (sqlDr.Read())
                    {
                        UserFileCabinetUploadFile userFileCabinetUploadFile = new UserFileCabinetUploadFile();
                        userFileCabinetUploadFile.FileId = (int)sqlDr["FileId"];
                        userFileCabinetUploadFile.FileName = sqlDr["FileName"].ToString();
                        userFileCabinetUploadFile.FilePath = sqlDr["FilePath"].ToString();
                        userFileCabinetUploadFile.FileUploadTime = (DateTime)sqlDr["FileUploadTime"];
                        list.Add(userFileCabinetUploadFile);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据文件编号删除该文件
        /// </summary>
        /// <param name="fid"></param>
        /// <returns></returns>
        public int delUploadFileByFileId(int fid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from UserFileCabinetUploadFile where FileId = @fid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@fid", fid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据文件编号查找文件信息
        /// </summary>
        /// <param name="fid"></param>
        /// <returns></returns>
        public UserFileCabinetUploadFile findByFileId(int fid)
        {
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            UserFileCabinetUploadFile userFileCabinetUploadFile = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserFileCabinetUploadFile where FileId = @Fid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@Fid", fid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    userFileCabinetUploadFile = new UserFileCabinetUploadFile();
                    sqlDr.Read();
                    userFileCabinetUploadFile.FileId = (int)sqlDr["FileId"];
                    userFileCabinetUploadFile.FileName = sqlDr["FileName"].ToString();
                    userFileCabinetUploadFile.FilePath = sqlDr["FilePath"].ToString();
                    userFileCabinetUploadFile.FileUploadTime = (DateTime)sqlDr["FileUploadTime"];
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return userFileCabinetUploadFile;
        }
    }
}
